package com.walker.service.catalogue;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.util.CommonUtils;

@Service
public class CatalogueDerivateIndexService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	@Autowired
	@Qualifier("dsJdbcTemplate")
	private JdbcTemplate dsJdbcTemplate;
	
	/**
	 * 指标列表
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryDeriveList(Map<String,Object> params){
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize ;
		
		List<Object> paramList = new ArrayList<Object>();
		String queryConf = getDeriveConf(params, paramList);
		
		String countSql = getDeriveCountSql(queryConf);
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		String sql = getDeriveSql(queryConf);
		paramList.add(pageSize);
		paramList.add(offset);
		List<Map<String,Object>> deriList = dmJdbcTemplate.queryForList(sql,paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", totalRecords);
		data.put("derive_data", deriList);
		
		return data;
	}

	/**
	 * 产出信息
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryTaskList(Map<String,Object> params){
		String id = CommonUtils.valueOf(params.get("id"));
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize ;
		
		String countSql = "select count(1) count from t_ds_process_instance where name like ?";
		Object count = dsJdbcTemplate.queryForMap(countSql,"%"+id+"%").get("count");
		
		String sql = getTaskSql();
		List<Map<String,Object>> list = dsJdbcTemplate.queryForList(sql,"%"+id+"%",pageSize,offset);
		
		sql = getDeriveSql().append(" and deri.id = ?").toString();
		Map<String,Object> basicInfo = dmJdbcTemplate.queryForMap(sql,id);
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", count);
		data.put("task_data", list);
		data.put("basic_data", basicInfo);
		return data;
	}

	/**
	 * 项目信息
	 * @return
	 */
	public List<Map<String,Object>> queryProjectInfo(){
		String sql = "select id as project_id,project_code,project_name from t_meta_project";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 维度表信息
	 * @param project_id
	 * @return
	 */
	public List<Map<String,Object>> queryDimInfo(String project_id){
		List<Object> paramList = new ArrayList<Object>();
		String sql = "select dim_id,dim_code,dim_name from t_meta_dimension";
		if(StringUtils.isNotBlank(project_id)){
			sql = sql + " where project_id = ?";
			paramList.add(project_id);
		}
		return dmJdbcTemplate.queryForList(sql,paramList.toArray());
	}
	
	/**
	 * 原子指标信息
	 * @param params
	 * @return
	 */
	public List<Map<String,Object>> queryAtomicInfo(Map<String,Object> params){
		String project_id = CommonUtils.valueOf(params.get("project_id"));
		String dim_id = CommonUtils.valueOf(params.get("dim_id"));
		String logic_fact_id = CommonUtils.valueOf(params.get("logic_fact_id"));
		List<Object> paramList = new ArrayList<Object>();
		String sql = "select id as atomic_id,atomic_code,atomic_name from t_meta_atomic_index where 1 = 1";
		if(StringUtils.isNotBlank(project_id)){
			sql = sql + " and project_id = ?";
			paramList.add(project_id);
		}
		if(StringUtils.isNotBlank(logic_fact_id) || StringUtils.isNotBlank(dim_id)){
			sql = sql + " and (fact_logic_id in (select id from t_meta_fact_logic where fact_logic_id = ?) or dim_logic_id in(select id from t_meta_dim_logic where dim_id = ?))";
			paramList.add(logic_fact_id);
			paramList.add(dim_id);
		}
		return dmJdbcTemplate.queryForList(sql,paramList.toArray());
	}
	
	/**
	 * 事实逻辑表信息
	 * @param project_id
	 * @return
	 */
	public List<Map<String,Object>> queryFactLogicInfo(String project_id){
		List<Object> paramList = new ArrayList<Object>();
		String sql = "select id as logic_fact_id,logic_fact_code,logic_fact_name from t_meta_fact_logic_bases";
		if(StringUtils.isNotBlank(project_id)){
			sql = sql + " where project_id = ?";
			paramList.add(project_id);
		}
		return dmJdbcTemplate.queryForList(sql,paramList.toArray());
	}
	
	/**
	 * 汇总逻辑表信息
	 * @param params
	 * @return
	 */
	public List<Map<String,Object>> querySumLogicInfo(Map<String,Object> params){
		String project_id = CommonUtils.valueOf(params.get("project_id"));
		String dim_id = CommonUtils.valueOf(params.get("dim_id"));
		List<Object> paramList = new ArrayList<Object>();
		String sql = "select id as sum_id,sum_code,sum_name from t_meta_summary where 1 = 1";
		if(StringUtils.isNotBlank(project_id)){
			sql = sql + " and project_id = ?";
			paramList.add(project_id);
		}
		if(StringUtils.isNotBlank(dim_id)){
			sql = sql + " and id in(select sum_id from t_meta_sum_relation where object_type = '16' and index_id in(select id from t_meta_dim_logic tmdl where dim_id = ?))";
			paramList.add(dim_id);
		}
		return dmJdbcTemplate.queryForList(sql,paramList.toArray());
	}
	
	private String getDeriveConf(Map<String,Object> params,List<Object> paramList) {
		String project_id = CommonUtils.valueOf(params.get("project_id"));
		String dim_id = CommonUtils.valueOf(params.get("dim_id"));
		String logic_fact_id = CommonUtils.valueOf(params.get("logic_fact_id"));
		String atomic_id = CommonUtils.valueOf(params.get("atomic_id"));
		String sum_id = CommonUtils.valueOf(params.get("sum_id"));
		String keyword = CommonUtils.valueOf(params.get("keyword"));
		
		StringBuffer queryConf = new StringBuffer();
		if(StringUtils.isNotBlank(project_id)){
			queryConf.append(" and deri.project_id = ?");
			paramList.add(project_id);
		}
		if(StringUtils.isNotBlank(dim_id)){
			queryConf.append(" and(deri.dim_id_1 = ? or deri.dim_id_2 = ? or deri.dim_id_3 = ?)");
			paramList.add(dim_id);
			paramList.add(dim_id);
			paramList.add(dim_id);
		}
		if(StringUtils.isNotBlank(logic_fact_id)){
			queryConf.append(" and deri.source_id = ?");
			paramList.add(logic_fact_id);
		}
		if(StringUtils.isNotBlank(atomic_id)){
			queryConf.append(" and deri.atomic_id = ?");
			paramList.add(atomic_id);
		}
		if(StringUtils.isNotBlank(sum_id)){
			queryConf.append(" and summary.id = ?");
			paramList.add(sum_id);
		}
		if(StringUtils.isNotBlank(keyword)){
			queryConf.append(" and (derivative_code like ? or derivative_name like ?)");
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		return queryConf.toString();
	}

	private String getDeriveSql(String queryConf) {
		StringBuffer sql = getDeriveSql();
		sql.append(queryConf);
		sql.append(" order by deri.oper_time desc ");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}

	private StringBuffer getDeriveSql() {
		StringBuffer sql = new StringBuffer();
		sql.append("select deri.id,");
		sql.append("       derivative_code,");
		sql.append("       derivative_name,");
		sql.append("       deri.project_name,");
		sql.append("       concat_ws('&',");
		sql.append("                 (select concat(dim_code)");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_1),");
		sql.append("                 (select dim_code");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_2),");
		sql.append("                 (select dim_code");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_3)) as dim_code,");
		sql.append("       concat_ws('&',");
		sql.append("                 (select concat(dim_name)");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_1),");
		sql.append("                 (select dim_name");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_2),");
		sql.append("                 (select dim_name");
		sql.append("                    from t_meta_dimension dim");
		sql.append("                   where dim.dim_id = deri.dim_id_3)) as dim_name,");
		sql.append("       fact.logic_fact_code,");
		sql.append("       fact.logic_fact_name,");
		sql.append("       summary.sum_code,");
		sql.append("       summary.sum_name,");
		sql.append("       deri.derivative_desc,");
		sql.append("       ato.atomic_code,");
		sql.append("       ato.atomic_name,");
		sql.append("       deri.oper_user,");
		sql.append("       cyc.cycle_name,");
		sql.append("       con.condition_name,");
		sql.append("       deri.data_type");
		sql.append("  from t_meta_derivative_index deri");
		sql.append("  left join t_meta_fact_logic_bases fact");
		sql.append("    on deri.source_id = fact.id");
		sql.append("  left join t_meta_sum_relation sum_rel");
		sql.append("    on deri.id = sum_rel.index_id");
		sql.append("  left join t_meta_summary summary");
		sql.append("    on sum_rel.sum_id = summary.id");
		sql.append("  left join t_meta_atomic_index ato");
		sql.append("    on deri.atomic_id = ato.id");
		sql.append("  left join t_meta_cycle cyc");
		sql.append("    on deri.cycle_id = cyc.id");
		sql.append("  left join t_meta_condition con");
		sql.append("    on deri.condition_id = con.id");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	private String getDeriveCountSql(String queryConf) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count");
		sql.append("  from t_meta_derivative_index deri ");
		sql.append("  left join t_meta_sum_relation sum_rel ");
		sql.append("    on deri.id = sum_rel.index_id ");
		sql.append("  left join t_meta_summary summary");
		sql.append("    on sum_rel.sum_id = summary.id");
		sql.append(" where 1 = 1");
		sql.append(queryConf);
		return sql.toString();
	}
	
	private String getTaskSql() {
		StringBuffer sql = new StringBuffer();
		sql.append("select name,");
		sql.append("	   case when state = 0 then '提交成功'");
		sql.append("            when state = 1 then '正在执行'");
		sql.append("            when state = 2 then '准备暂停'");
		sql.append("            when state = 3 then '暂停'");
		sql.append("            when state = 4 then '准备停止'");
		sql.append("            when state = 5 then '停止'");
		sql.append("            when state = 6 then '失败'");
		sql.append("            when state = 7 then '成功'");
		sql.append("            when state = 8 then '需要容错'");
		sql.append("            when state = 9 then 'Kill'");
		sql.append("            when state = 10 then '等待线程'");
		sql.append("            when state = 11 then '等待依赖完成'");
		sql.append("            when state = 12 then '延迟执行'");
		sql.append("            when state = 13 then '强制成功' end as status,");
		sql.append("	   date_format(start_time,'%Y-%m-%d %H:%i:%s') as start_time,");
		sql.append("	   date_format(end_time,'%Y-%m-%d %H:%i:%s') as end_time,");
		sql.append("	  ifnull(concat(");
		sql.append("	    if(floor(hour(timediff(end_time,start_time))/24)=0,'',concat(floor(hour(timediff(end_time,start_time))/24),'d ')),");
		sql.append("		if(mod(hour(timediff(end_time,start_time)),24)=0,'',concat(mod(hour(timediff(end_time,start_time)),24), 'h ')),");
		sql.append("		if(minute(timediff(end_time,start_time))=0,'',concat(minute(timediff(end_time,start_time)),'m ')),");
		sql.append("		if(second(timediff(end_time,start_time))=0,'',concat(second(timediff(end_time,start_time)),'s'))),'-') as duration");
		sql.append(" from t_ds_process_instance");
		sql.append(" where name like ?");
		sql.append(" order by schedule_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}
}